/**
 * @date 2005-12-10 20:40:41
 *
 */
package org.nanhill.support.poi;

import static org.apache.poi.hssf.usermodel.HSSFCell.CELL_TYPE_NUMERIC;
import static org.apache.poi.hssf.usermodel.HSSFCell.CELL_TYPE_STRING;

import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;

/**
 * @author alin
 * 
 */
public class HSSFKit {
	/**
	 * Convenient method to obtain the cell in the given sheet, row and column.
	 * <p>
	 * Creates the row and the cell if they still doesn't already exist. Thus,
	 * the column can be passed as an int, the method making the needed
	 * downcasts.
	 * 
	 * @param sheet
	 *            a sheet object. The first sheet is usually obtained by
	 *            workbook.getSheetAt(0)
	 * @param row
	 *            thr row number
	 * @param col
	 *            the column number
	 * @return the HSSFCell
	 */
	public static HSSFCell getCell(HSSFSheet sheet, int row, int col) {
		HSSFRow sheetRow = sheet.getRow(row);
		if (sheetRow == null) {
			sheetRow = sheet.createRow(row);
		}
		HSSFCell cell = sheetRow.getCell((short) col);
		if (cell == null) {
			cell = sheetRow.createCell((short) col);
		}
		return cell;
	}

	public static String getValue(HSSFCell cell) {
		int type = cell.getCellType();
		switch (type) {
		case (HSSFCell.CELL_TYPE_STRING):
			return cell.getStringCellValue();
		case (HSSFCell.CELL_TYPE_NUMERIC):
			double value = cell.getNumericCellValue();
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				final DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
				return df.format(HSSFDateUtil.getJavaDate(value));
			} else {
				final NumberFormat nf = new DecimalFormat("#");
				return nf.format(value);
			}
		}
		return "";
	}

	public static void printSheet(HSSFSheet sheet) {
		printSheet(sheet, 0, 0);
	}

	/**
	 * print specified region of excel sheet
	 * 
	 * @param sheet
	 */
	public static void printSheet(HSSFSheet sheet, int rowStartIndex,
			int colStartIndex) {
		HSSFRow row = null;
		HSSFCell cell = null;
		String v = null;
		for (int i = rowStartIndex; i < sheet.getPhysicalNumberOfRows(); i++) {
			row = sheet.getRow(i);
			if (null != row) {
				for (int j = colStartIndex; j < sheet.getRow(0)
						.getPhysicalNumberOfCells(); j++) {
					cell = row.getCell((short) j);
					if (null == cell) {
						v = "[null]";
					} else {
						switch (cell.getCellType()) {
						case CELL_TYPE_STRING:
							v = "(" + cell.getStringCellValue() + ")";
							break;
						case CELL_TYPE_NUMERIC:
							double num = cell.getNumericCellValue();
							if (HSSFDateUtil.isCellDateFormatted(cell)) {
								v = "{" + HSSFDateUtil.getJavaDate(num) + "}";
							} else {
								v = "["
										+ Double.toString(cell
												.getNumericCellValue()) + "]";
							}
							break;
						default:
							v = "$error type$";
						}
					}
					System.out.print(v + "|\t");
				}
			}
			System.out
					.println("\n----------------------------------------------------------");
		}
	}
}
